//
//  DBStorageUpdater.m
//  IsolatedStorageDB
//
//  Created by Fanty on 13-12-25.
//  Copyright (c) 2013年 Fanty. All rights reserved.
//

#import "DBStorageUpdater.h"

#import "ChamleonDB.h"

@interface DBStorageUpdater()

+(BOOL)updateTable:(NSString*)tableName originDatabase:(ChamleonDB*)originDatabase newSql:(NSString*)newSql;

+(NSArray*)filterSql:(NSString*)sql;

@end

@implementation DBStorageUpdater

+(void)createNewTable:(NSString*)originDB  newDB:(NSString*)newDB{
    ChamleonDB* originDatabase=[ChamleonDB databaseWithPath:originDB];
    ChamleonDB* newDatabase=[ChamleonDB databaseWithPath:newDB];
    
    [originDatabase open];
    [newDatabase open];
    
    NSMutableArray* originTables=[[NSMutableArray alloc] initWithCapacity:2];
    NSMutableArray* newTables=[[NSMutableArray alloc] initWithCapacity:2];
    NSMutableArray* newTablesSql=[[NSMutableArray alloc] initWithCapacity:2];
    NSString* sql=@"SELECT name,sql FROM main.sqlite_master where type='table'";
    
    ChamleonResultSet* result=[originDatabase executeQuery:sql];

    while([result next]){
        [originTables addObject:[result stringForColumnIndex:0]];
    }
    [result close];
    
    result=[newDatabase executeQuery:sql];
    
    while([result next]){
        [newTables addObject:[result stringForColumnIndex:0]];
        [newTablesSql addObject:[result stringForColumnIndex:1]];
    }
    [result close];
    
    
    [originDatabase beginTransaction];
    //先删除旧数据库里不用的表
    int index=0;
    while(true){
        if(index>(int)[originTables count]-1)break;
        NSString* originTableName=[originTables objectAtIndex:index];
        BOOL exists=NO;
        for(NSString* newTableName in newTables){
            if([originTableName isEqualToString:newTableName]){
                exists=YES;
                break;
            }
        }
        if(!exists){
            [originDatabase executeUpdate:[NSString stringWithFormat:@"DROP TABLE %@",originTableName]];
            [originTables removeObjectAtIndex:index];
        }
        else{
            index++;
        }
    }
    
    //然后将新数据库的新的表，添加进去旧数据库里
    index=0;
    while(true){
        if(index>(int)[newTables count]-1)break;
        NSString* newTableName=[newTables objectAtIndex:index];
        BOOL exists=NO;
        for(NSString* originTableName in originTables){
            if([newTableName isEqualToString:originTableName]){
                exists=YES;
                break;
            }
        }
        if(!exists){
            [originDatabase executeUpdate:[newTablesSql objectAtIndex:index]];
            [originTables addObject:newTableName];
        }
        else{
            index++;
        }
    }
    
    [originDatabase commit];
    
    [newDatabase close];

    
    [originTables removeAllObjects];
    
    for(int i=0;i<[newTables count];i++){
        NSString* tableName=[newTables objectAtIndex:i];
        @autoreleasepool {
            if(![DBStorageUpdater updateTable:tableName originDatabase:originDatabase newSql:[newTablesSql objectAtIndex:i]]){
                [originDatabase beginTransaction];
                
                [originDatabase executeUpdate:[NSString stringWithFormat:@"DROP TABLE %@",tableName]];
                [originDatabase executeUpdate:[newTablesSql objectAtIndex:i]];
                
                [originDatabase commit];
            }
        }
    }
    
    [newTablesSql removeAllObjects];
    

    
    [originDatabase close];
}


+(BOOL)updateTable:(NSString*)tableName originDatabase:(ChamleonDB*)originDatabase newSql:(NSString*)newSql{
    BOOL hasTableData=NO;
    
    
    NSMutableDictionary* originColumnDict=[[NSMutableDictionary alloc] initWithCapacity:1];
    
    ChamleonResultSet* resultSet=[originDatabase executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@",tableName]];
    
    //先拿出旧表的所有字段
    if([resultSet next]){
        hasTableData=YES;
        int count=[resultSet columnCount];
        for(int i=0;i<count;i++){
            int columnType = sqlite3_column_type([[resultSet statement] statement], i);
            
            NSNumber* numberValue=[NSNumber numberWithInt:columnType];
            
            [originColumnDict setObject:numberValue forKey:[resultSet columnNameForIndex:i]];
        }
    }
    [resultSet close];
    
    if(hasTableData){
        //如果旧表有数据的话，  就拿出新表的所有字段
        
        NSArray* newColumnDict=[DBStorageUpdater filterSql:newSql];
        
        [originDatabase beginTransaction];
        
        [newColumnDict enumerateObjectsUsingBlock:^(id obj,NSUInteger index,BOOL*stop){
        
            NSDictionary* dict=obj;
            
            NSString* new_columnName=[dict objectForKey:@"columnName"];
            NSString* new_columnType=[dict objectForKey:@"columnType"];
            NSNumber* origin_columnType=[originColumnDict objectForKey:new_columnName];
            
            NSString* dv=@"";
            NSString* defaultValue=[dict objectForKey:@"default"];
            if([defaultValue length]>0){
                dv=[NSString stringWithFormat:@"DEFAULT %@",defaultValue];
            }
            NSString* sql=nil;
            if(origin_columnType==nil){       //没有这个列
                //DEFAULT 0
                sql=[NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ %@ %@",tableName,new_columnName,new_columnType,dv];

            }
            else{
                sql=[NSString stringWithFormat:@"ALTER TABLE %@ ALTER COLUMN %@ %@ %@",tableName,new_columnName,new_columnType,dv];
            }
            if([originDatabase executeUpdate:sql]){
                
            }

        
        }];
        
        [originDatabase commit];
        
    }
    
    return hasTableData;
}


+(NSArray*)filterSql:(NSString*)sql{
    
    NSMutableArray* list=[[NSMutableArray alloc] initWithCapacity:2];
    NSRange startRange=[sql rangeOfString:@"("];
    sql=[sql substringFromIndex:startRange.location+1];

    startRange.location=0;
    startRange.length=[sql length]-1;
    sql=[sql substringWithRange:startRange];

    
    NSArray* array=[sql componentsSeparatedByString:@","];
    
    for(NSString* _sql in array){
        ;
        NSArray* columns=[[_sql stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]] componentsSeparatedByString:@" "];
        NSString* columnName=[columns objectAtIndex:0];
    
        NSRange range;
        range.location=1;
        range.length=[columnName length]-2;
        columnName=[columnName substringWithRange:range];
        
        NSString* columnType=[columns objectAtIndex:1];
        NSString* columnParam=nil;
        NSString* columnParamKey=@"columnParam";
        if([columns count]>2){
            columnParam=[[columns objectAtIndex:2] lowercaseString];
            if([columnParam rangeOfString:@"default"].length>0 && [columns count]>3){
                columnParamKey=@"default";
                columnParam=[columns objectAtIndex:3];
                range.location=1;
                range.length=[columnParam length]-2;
                columnParam=[columnParam substringWithRange:range];
            }
        }
        NSDictionary* dict=[[NSDictionary alloc] initWithObjectsAndKeys:
                            columnName,@"columnName",
                            columnType,@"columnType",
                            columnParam,columnParamKey,
                            nil];
        
        [list addObject:dict];
    }
    
    return list;
}

@end
